package cn.dida.dao;

import cn.dida.model.Pageable;
import cn.dida.util.DateUtils;
import org.apache.commons.lang.ArrayUtils;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhangzhiling on 2017/9/1.
 */
@Repository
public class SalesmanCashDao extends DaoBase {

    /**
     * 提现申请 数据列表
     * @param companyId
     * @param proName
     * @param saleman
     * @param timeLimitStart
     * @param timeLimitEnd
     * @param page
     * @return
     */
    public Pageable<Map<String, Object>> queryList(Integer companyId, String proName, String saleman, String timeLimitStart, String timeLimitEnd, Integer page){

        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from salesman_cash c  join" +
                " salesman s on s.id = c.salesman_id join product p on p.id=c.product_id");
        StringBuffer sqlQueryResult = new StringBuffer("select c.id,c.code, c.num, c.c_time, c.pay_state,c.unitprize," +
                " c.totalprize,s.id 'salesmanId', s.name 'salesmanName', s.code 'salesmanCode',s.weixin_openid 'openid', " +
                "p.name 'proName', p.id 'proId' " +
                " from salesman_cash c  join  salesman s on s.id = c.salesman_id join product p on p.id=c.product_id" );

        StringBuffer sqlWhere = new StringBuffer(" where c.company_id=? and c.pay_state != 1");
        args = ArrayUtils.add(args, companyId);

        if (proName != null && proName !="") {
            sqlWhere.append(" and p.name like ?");
            args = ArrayUtils.add(args, "%" + proName + "%");
        }
        if (saleman != null && saleman !="") {
            sqlWhere.append(" and (s.name like ? or s.code like ?)");
            args = ArrayUtils.add(args, "%" + saleman + "%");
            args = ArrayUtils.add(args, "%" + saleman + "%");
        }
        if (timeLimitStart != null && timeLimitStart !="") {
            sqlWhere.append(" and c.c_time >= ?");
            try {
                timeLimitStart = String.valueOf(DateUtils.stringToLong(timeLimitStart, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitStart);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }
        if (timeLimitEnd != null && timeLimitEnd !="") {
            sqlWhere.append(" and c.c_time <= ?");
            try {
                timeLimitEnd = String.valueOf(DateUtils.stringToLong(timeLimitEnd, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitEnd);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c.c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 提现申请 数据导出
     * @param companyId
     * @param proName
     * @param saleman
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public List<Map<String, Object>> exportList(Integer companyId, String proName, String saleman, String timeLimitStart, String timeLimitEnd){
        Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select c.id,c.code, c.num, c.c_time, c.pay_state," +
                " s.id 'salesmanId', s.name 'salesmanName', s.code 'salesmanCode',s.weixin_openid 'openid', " +
                "p.name 'proName', p.id 'proId' " +
                " from salesman_cash c  join  salesman s on s.id = c.salesman_id join product p on p.id=c.product_id" );

        StringBuffer sqlWhere = new StringBuffer(" where c.company_id=? and c.pay_state != 1");
        args = ArrayUtils.add(args, companyId);

        if (proName != null && proName !="") {
            sqlWhere.append(" and p.name like ?");
            args = ArrayUtils.add(args, "%" + proName + "%");
        }
        if (saleman != null && saleman !="") {
            sqlWhere.append(" and (s.name like ? or s.code like ?)");
            args = ArrayUtils.add(args, "%" + saleman + "%");
            args = ArrayUtils.add(args, "%" + saleman + "%");
        }
        if (timeLimitStart != null && timeLimitStart !="") {
            sqlWhere.append(" and c.c_time >= ?");
            try {
                timeLimitStart = String.valueOf(DateUtils.stringToLong(timeLimitStart, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitStart);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }
        if (timeLimitEnd != null && timeLimitEnd !="") {
            sqlWhere.append(" and c.c_time <= ?");
            try {
                timeLimitEnd = String.valueOf(DateUtils.stringToLong(timeLimitEnd, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitEnd);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }

        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c.c_time desc");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return result;
    }

    /**
     * 支付时 跟新状态与message_id
     * @param cashId
     * @param salesman_id
     * @param message_id
     */
    public void update_state2(Integer cashId, Integer salesman_id,String message_id) {
        String sql = "UPDATE salesman_cash SET pay_state =:state,pay_message_id =:message_id where id = "+cashId+" and  salesman_id = "+salesman_id;

        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("state", 2);
        paramMap.put("message_id", message_id);
        namedParameterJdbcTemplate.update(sql,paramMap);
    }

    /**
     * 支付成功或失败 回调
     * @param message_id
     */
    public void update_callback( Integer state,String message_id,String descripe) {

        String sql = "UPDATE salesman_cash SET pay_state=:state ,pay_time=:nowTime ," +
                "pay_false_info=:pay_false_info,pay_price=totalprize where pay_message_id =:message_id"  ;
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("state", state);
        paramMap.put("nowTime", new Date().getTime());
        paramMap.put("pay_false_info", descripe);
        paramMap.put("message_id",message_id);
        namedParameterJdbcTemplate.update(sql,paramMap);

    }

    /**
     * 获取已支付列表
     * @param companyId
     * @param proName
     * @param saleman
     * @param timeLimitStart
     * @param timeLimitEnd
     * @param page
     * @return
     */
    public Pageable<Map<String, Object>> queryPaidList(Integer companyId, String proName, String saleman, String timeLimitStart, String timeLimitEnd, Integer page){
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from salesman_cash c  join" +
                " salesman s on s.id = c.salesman_id join product p on p.id=c.product_id");
        StringBuffer sqlQueryResult = new StringBuffer("select c.code, c.num, c.c_time, c.pay_state,c.pay_time,c.pay_price," +
                " s.name 'salesmanName', s.code 'salesmanCode',s.phone, p.name 'proName' " +
                " from salesman_cash c  join  salesman s on s.id = c.salesman_id join product p on p.id=c.product_id" );

        StringBuffer sqlWhere = new StringBuffer(" where c.company_id=? and c.pay_state = 1");
        args = ArrayUtils.add(args, companyId);

        if (proName != null && proName !="") {
            sqlWhere.append(" and p.name like ?");
            args = ArrayUtils.add(args, "%" + proName + "%");
        }
        if (saleman != null && saleman !="") {
            sqlWhere.append(" and (s.name like ? or s.phone like ?)");
            args = ArrayUtils.add(args, "%" + saleman + "%");
            args = ArrayUtils.add(args, "%" + saleman + "%");
        }
        if (timeLimitStart != null && timeLimitStart != "") {
            sqlWhere.append(" and c.pay_time >= ?");
            try {
                timeLimitStart = String.valueOf(DateUtils.stringToLong(timeLimitStart, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitStart);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }
        if (timeLimitEnd != null && timeLimitEnd !="") {
            sqlWhere.append(" and c.pay_time <= ?");
            try {
                timeLimitEnd = String.valueOf(DateUtils.stringToLong(timeLimitEnd, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitEnd);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c.c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 已支付导出
     * @param companyId
     * @param proName
     * @param saleman
     * @param timeLimitStart
     * @param timeLimitEnd
     * @return
     */
    public List<Map<String, Object>> exportPaidList(Integer companyId, String proName, String saleman, String timeLimitStart, String timeLimitEnd){
        Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select c.code, c.num, c.c_time, c.pay_state,c.pay_time,c.pay_price," +
                " s.name 'salesmanName', s.code 'salesmanCode',s.phone, p.name 'proName' " +
                " from salesman_cash c  join  salesman s on s.id = c.salesman_id join product p on p.id=c.product_id" );

        StringBuffer sqlWhere = new StringBuffer(" where c.company_id=? and c.pay_state = 1");
        args = ArrayUtils.add(args, companyId);

        if (proName != null && proName !="") {
            sqlWhere.append(" and p.name like ?");
            args = ArrayUtils.add(args, "%" + proName + "%");
        }
        if (saleman != null && saleman !="") {
            sqlWhere.append(" and (s.name like ? or s.phone like ?)");
            args = ArrayUtils.add(args, "%" + saleman + "%");
            args = ArrayUtils.add(args, "%" + saleman + "%");
        }
        if(timeLimitStart != null && timeLimitStart != ""){
            sqlWhere.append(" and c.pay_time >= ?");
            try {
                timeLimitStart = String.valueOf(DateUtils.stringToLong(timeLimitStart, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitStart);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }
        if(timeLimitEnd != null && timeLimitEnd != ""){
            sqlWhere.append(" and c.pay_time <= ?");
            try {
                timeLimitEnd = String.valueOf(DateUtils.stringToLong(timeLimitEnd, "yyyy-MM-dd HH:mm:ss"));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            long ctimes = Long.parseLong(timeLimitEnd);
            args = org.apache.commons.lang3.ArrayUtils.add(args, ctimes);
        }

        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c.c_time desc ");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return result;
    }



}
